import sys
import os
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

import sqlite3
from datetime import datetime, timedelta
import pandas as pd
import numpy as np
from typing import List, Optional, Union
import os
import tempfile
from database.db import get_db_connection
from models import FaultRecord, FaultCreate, FaultUpdate, FaultStats, ManufacturerStats, FaultTrend

# 修复pandas类型问题的辅助函数
def _get_dataframe_shape(df: pd.DataFrame) -> tuple:
    """获取DataFrame的形状"""
    return df.shape

def _get_dataframe_columns(df: pd.DataFrame) -> list:
    """获取DataFrame的列名"""
    return list(df.columns)

def _get_dataframe_head(df: pd.DataFrame, n: int = 5) -> pd.DataFrame:
    """获取DataFrame的前n行"""
    return df.head(n)

def _is_dataframe_empty(df: pd.DataFrame) -> bool:
    """检查DataFrame是否为空"""
    return df.empty

def _dataframe_iterrows(df: pd.DataFrame):
    """遍历DataFrame的行"""
    return df.iterrows()

class FaultService:
    """故障管理服务类 - 业务逻辑层"""
    
    def create_fault(self, fault: FaultCreate) -> FaultRecord:
        """创建故障记录"""
        try:
            conn = get_db_connection()
            cursor = conn.cursor()
            
            # 添加日志以调试接收到的数据
            print(f"创建故障记录，接收到的数据: {fault}")
            
            cursor.execute('''
                INSERT INTO fault_statistics (
                    factory_name, host_name, fault_time, alarm_name, level,
                    device_sn, resolution, recovery_time, note1, note2
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                fault.factory_name, fault.host_name, fault.fault_time.isoformat(), fault.alarm_name, fault.level,
                fault.device_sn, fault.resolution, 
                fault.recovery_time.isoformat() if fault.recovery_time else None,
                fault.note1, fault.note2
            ))
            
            fault_id = cursor.lastrowid
            conn.commit()
            conn.close()
            
            return self.get_fault_by_id(fault_id)  # type: ignore
        except Exception as e:
            print(f"创建故障记录时发生错误: {e}")
            raise e
    
    def create_fault_with_connection(self, conn: sqlite3.Connection, fault: FaultCreate) -> FaultRecord:
        """使用现有数据库连接创建故障记录"""
        try:
            cursor = conn.cursor()
            
            # 添加日志以调试接收到的数据
            print(f"创建故障记录，接收到的数据: {fault}")
            
            cursor.execute('''
                INSERT INTO fault_statistics (
                    factory_name, host_name, fault_time, alarm_name, level,
                    device_sn, resolution, recovery_time, note1, note2
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                fault.factory_name, fault.host_name, fault.fault_time.isoformat(), fault.alarm_name, fault.level,
                fault.device_sn, fault.resolution, 
                fault.recovery_time.isoformat() if fault.recovery_time else None,
                fault.note1, fault.note2
            ))
            
            fault_id = cursor.lastrowid
            conn.commit()
            
            # 获取刚创建的记录
            cursor.execute("SELECT * FROM fault_statistics WHERE id = ?", (fault_id,))
            row = cursor.fetchone()
            
           #这段代码的目的是确保新插入的记录能够被正确返回给调用者，同时将数据库中的字符串时间格式转换为
           #Python 的 datetime 对象，保证数据类型的一致性。
            if row: #- 检查是否从数据库查询中获取到了记录
                # 处理时间字段
                row_dict = dict(row)    #- 将数据库返回的行数据转换为字典格式
                row_dict['fault_time'] = datetime.fromisoformat(row_dict['fault_time']) #- 将故障时间从字符串转换为 datetime 对象
                if row_dict['recovery_time']:   # - 检查恢复时间是否存在- 如果存在，也将其从字符串转换为 datetime 对象
                    row_dict['recovery_time'] = datetime.fromisoformat(row_dict['recovery_time'])
                return FaultRecord(**row_dict)  #- 使用字典数据创建并返回一个 FaultRecord 对象
            else:
                raise Exception("创建故障记录后无法获取记录")   # - 如果查询不到刚插入的记录，则抛出异常
        except Exception as e:
            print(f"创建故障记录时发生错误: {e}")
            raise e
    
    def get_fault_by_id(self, fault_id: int) -> Optional[FaultRecord]:
        """根据ID获取故障记录"""
        conn = get_db_connection()
        cursor = conn.cursor()
        
        cursor.execute("SELECT * FROM fault_statistics WHERE id = ?", (fault_id,))
        row = cursor.fetchone()
        conn.close()
        
        if row:
            # 处理时间字段
            row_dict = dict(row)
            row_dict['fault_time'] = datetime.fromisoformat(row_dict['fault_time'])
            if row_dict['recovery_time']:
                row_dict['recovery_time'] = datetime.fromisoformat(row_dict['recovery_time'])
            return FaultRecord(**row_dict)
        return None
    
    def get_faults(self, skip: int = 0, limit: int = 100, search: Optional[str] = None) -> List[FaultRecord]:
        """获取故障记录列表"""
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 保留search参数以保持向后兼容性，但不再使用
        cursor.execute('''
            SELECT * FROM fault_statistics 
            ORDER BY fault_time DESC
            LIMIT ? OFFSET ?
        ''', (limit, skip))
        
        rows = cursor.fetchall()
        conn.close()
        
        faults = []
        for row in rows:
            # 处理时间字段
            row_dict = dict(row)
            row_dict['fault_time'] = datetime.fromisoformat(row_dict['fault_time'])
            if row_dict['recovery_time']:
                row_dict['recovery_time'] = datetime.fromisoformat(row_dict['recovery_time'])
            faults.append(FaultRecord(**row_dict))
        
        return faults

    def get_faults_advanced(self, skip: int = 0, limit: int = 100, factory_names: Optional[List[str]] = None, 
                           host_names: Optional[List[str]] = None, alarm_names: Optional[List[str]] = None,
                           start_date: Optional[str] = None, end_date: Optional[str] = None, 
                           status: Optional[str] = None) -> List[FaultRecord]:
        """获取故障记录列表 - 高级搜索"""
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 构建查询条件
        conditions = []
        parameters = []
        
        # 厂家条件 - 改为模糊查询
        if factory_names:
            factory_conditions = []
            for factory_name in factory_names:
                factory_conditions.append("factory_name LIKE ?")
                parameters.append(f"%{factory_name}%")
            conditions.append(f"({') OR ('.join(factory_conditions)})")
        
        # 设备名称条件 - 改为模糊查询
        if host_names:
            host_conditions = []
            for host_name in host_names:
                host_conditions.append("host_name LIKE ?")
                parameters.append(f"%{host_name}%")
            conditions.append(f"({') OR ('.join(host_conditions)})")
        
        # 告警名称条件 - 改为模糊查询
        if alarm_names:
            alarm_conditions = []
            for alarm_name in alarm_names:
                alarm_conditions.append("alarm_name LIKE ?")
                parameters.append(f"%{alarm_name}%")
            conditions.append(f"({') OR ('.join(alarm_conditions)})")
        
        # 时间范围条件
        if start_date:
            conditions.append("DATE(fault_time) >= ?")
            parameters.append(start_date)
        
        if end_date:
            conditions.append("DATE(fault_time) <= ?")
            parameters.append(end_date)
        
        # 状态条件 - 修改逻辑，使其与前端和统计逻辑保持一致
        if status == "resolved":
            conditions.append("resolution IS NOT NULL AND resolution != 'nan' AND TRIM(resolution) != ''")
        elif status == "unresolved":
            conditions.append("resolution IS NULL OR resolution = 'nan' OR TRIM(resolution) = ''")
        
        # 构建完整查询
        base_query = "SELECT * FROM fault_statistics"
        if conditions:
            query = f"{base_query} WHERE {' AND '.join(conditions)} ORDER BY fault_time DESC LIMIT ? OFFSET ?"
        else:
            query = f"{base_query} ORDER BY fault_time DESC LIMIT ? OFFSET ?"
        
        parameters.extend([limit, skip])
        
        cursor.execute(query, parameters)
        rows = cursor.fetchall()
        conn.close()
        
        faults = []
        for row in rows:
            # 处理时间字段
            row_dict = dict(row)
            row_dict['fault_time'] = datetime.fromisoformat(row_dict['fault_time'])
            if row_dict['recovery_time']:
                row_dict['recovery_time'] = datetime.fromisoformat(row_dict['recovery_time'])
            faults.append(FaultRecord(**row_dict))
        
        return faults

    def get_faults_count(self, search: Optional[str] = None) -> int:
        """获取故障记录总数"""
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 保留search参数以保持向后兼容性，但不再使用
        cursor.execute('SELECT COUNT(*) FROM fault_statistics')
        
        count = cursor.fetchone()[0]
        conn.close()
        
        return count

    def get_faults_count_advanced(self, factory_names: Optional[List[str]] = None, 
                                 host_names: Optional[List[str]] = None, alarm_names: Optional[List[str]] = None,
                                 start_date: Optional[str] = None, end_date: Optional[str] = None, 
                                 status: Optional[str] = None) -> int:
        """获取故障记录总数 - 高级搜索"""
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 构建查询条件
        conditions = []
        parameters = []
        
        # 厂家条件 - 改为模糊查询
        if factory_names:   #检查factory_names参数是否存在且不为空
            factory_conditions = []    #创建一个空列表，用于存储厂家名称的查询条件
            for factory_name in factory_names:    #遍历所有输入的厂家名称筛选条件
                factory_conditions.append("factory_name LIKE ?")  #为每个厂家名称添加一个LIKE查询条件
                parameters.append(f"%{factory_name}%") #为每个LIKE查询添加参数
            conditions.append(f"({') OR ('.join(factory_conditions)})")    #将所有厂家名称条件用OR连接
        
        # 设备名称条件 - 改为模糊查询
        if host_names:  #- 检查是否有设备名称筛选条件
            host_conditions = []    #- 创建一个空列表，用于存储设备名称的查询条件
            for host_name in host_names:    #- 遍历所有输入的设备名称筛选条件
                host_conditions.append("host_name LIKE ?")  #- 为每个设备名称添加一个LIKE查询条件
                parameters.append(f"%{host_name}%") #- 为每个LIKE查询添加参数
            conditions.append(f"({') OR ('.join(host_conditions)})")    #- 将所有设备名称条件用OR连接
        
        # 告警名称条件 - 改为模糊查询
        if alarm_names:
            alarm_conditions = []
            for alarm_name in alarm_names:
                alarm_conditions.append("alarm_name LIKE ?")
                parameters.append(f"%{alarm_name}%")
            conditions.append(f"({') OR ('.join(alarm_conditions)})")
        
        # 时间范围条件
        if start_date:
            conditions.append("DATE(fault_time) >= ?")
            parameters.append(start_date)
        
        if end_date:
            conditions.append("DATE(fault_time) <= ?")
            parameters.append(end_date)
        
        # 状态条件 - 修改逻辑，使其与前端和统计逻辑保持一致
        if status == "resolved":
            conditions.append("resolution IS NOT NULL AND resolution != 'nan' AND TRIM(resolution) != ''")
        elif status == "unresolved":
            conditions.append("resolution IS NULL OR resolution = 'nan' OR TRIM(resolution) = ''")
        
        # 构建完整查询
        base_query = "SELECT COUNT(*) FROM fault_statistics"
        if conditions:
            query = f"{base_query} WHERE {' AND '.join(conditions)}"
        else:
            query = base_query
        
        cursor.execute(query, parameters)
        count = cursor.fetchone()[0]
        conn.close()
        
        return count
    
    def update_fault(self, fault_id: int, fault_update: FaultUpdate) -> Optional[FaultRecord]:
        """更新故障记录"""
        print(f"开始更新记录，ID: {fault_id}, 更新数据: {fault_update}")
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 构建更新语句
        update_fields = []
        update_values = []
        
        # 特别处理可能为空字符串的字段，确保空字符串被正确处理为NULL
        update_data = fault_update.dict(exclude_unset=True)
        
        # 处理resolution字段
        if 'resolution' in update_data:
            if update_data['resolution'] is not None and isinstance(update_data['resolution'], str) and update_data['resolution'].strip() == '':
                update_data['resolution'] = None
        
        # 处理其他可能为空字符串的字段
        optional_fields = ['level', 'device_sn', 'note1', 'note2']
        for field in optional_fields:
            if field in update_data:
                if update_data[field] is not None and isinstance(update_data[field], str) and update_data[field].strip() == '':
                    del update_data[field]
        
        for field, value in update_data.items():
            if value is not None:
                update_fields.append(f"{field} = ?")
                # 处理日期时间字段
                if isinstance(value, datetime):
                    update_values.append(value.isoformat())
                else:
                    update_values.append(value)
            else:
                # 如果值为None，设置为NULL
                update_fields.append(f"{field} = ?")
                update_values.append(None)
        
        if not update_fields:
            conn.close()
            result = self.get_fault_by_id(fault_id)
            return result
        
        update_values.append(fault_id)
        query = f"UPDATE fault_statistics SET {', '.join(update_fields)} WHERE id = ?"
        
        print(f"执行更新SQL: {query}, 参数: {update_values}")
        cursor.execute(query, update_values)
        affected_rows = cursor.rowcount
        print(f"更新影响行数: {affected_rows}")
        conn.commit()
        conn.close()
        
        result = self.get_fault_by_id(fault_id)
        print(f"更新后获取记录结果: {result}")
        return result
    
    def delete_fault(self, fault_id: int) -> bool:
        """删除故障记录"""
        conn = get_db_connection()
        cursor = conn.cursor()
        
        cursor.execute("DELETE FROM fault_statistics WHERE id = ?", (fault_id,))
        deleted_rows = cursor.rowcount
        conn.commit()
        conn.close()
        
        return deleted_rows > 0
    
    def get_fault_stats(self) -> FaultStats:
        """获取故障统计信息"""
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 获取总数量
        cursor.execute("SELECT COUNT(*) FROM fault_statistics")
        total_count = cursor.fetchone()[0]
        
        # 获取已解决数量（有处理结果的记录）
        # 修改统计逻辑，使其与前端保持一致：不为null、不为'nan'且不为空字符串（包括空白字符）
        cursor.execute("SELECT COUNT(*) FROM fault_statistics WHERE resolution IS NOT NULL AND resolution != 'nan' AND TRIM(resolution) != ''")
        resolved_count = cursor.fetchone()[0]
        
        # 获取未解决数量（没有处理结果的记录）
        # 修改统计逻辑，使其与前端保持一致：为null、为'nan'或为空字符串（包括空白字符）
        cursor.execute("SELECT COUNT(*) FROM fault_statistics WHERE resolution IS NULL OR resolution = 'nan' OR TRIM(resolution) = ''")
        unresolved_count = cursor.fetchone()[0]
        
        conn.close()
        
        return FaultStats(
            total_count=total_count,
            resolved_count=resolved_count,
            unresolved_count=unresolved_count
        )
    
    def get_manufacturer_stats(self, start_date: Optional[str] = None, end_date: Optional[str] = None) -> List[ManufacturerStats]:
        """获取厂家统计信息"""
        conn = get_db_connection()
        cursor = conn.cursor()
        
        if start_date and end_date:
            cursor.execute('''
                SELECT factory_name, COUNT(*) as fault_count
                FROM fault_statistics 
                WHERE DATE(fault_time) BETWEEN ? AND ?
                GROUP BY factory_name
                ORDER BY fault_count DESC
            ''', (start_date, end_date))
        else:
            cursor.execute('''
                SELECT factory_name, COUNT(*) as fault_count
                FROM fault_statistics 
                GROUP BY factory_name
                ORDER BY fault_count DESC
            ''')
        
        rows = cursor.fetchall()
        conn.close()
        
        # 返回每个厂家的总故障数，不区分处理结果
        result = []
        for row in rows:
            result.append(ManufacturerStats(
                factory_name=row['factory_name'],
                fault_count=row['fault_count'],
                resolution=None  # 不需要区分处理结果
            ))
        
        return result
    
    def get_fault_trend(self, days: int = 7) -> List[FaultTrend]:
        """获取故障趋势"""
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 获取最近days天的故障趋势
        cursor.execute('''
            SELECT DATE(fault_time) as date, COUNT(*) as count
            FROM fault_statistics 
            WHERE fault_time >= DATE('now', '-{} days')
            GROUP BY DATE(fault_time)
            ORDER BY date
        '''.format(days))
        
        rows = cursor.fetchall()
        conn.close()
        
        return [FaultTrend(**dict(row)) for row in rows]
    
    def export_faults_csv(self) -> List[FaultRecord]:
        """导出所有故障记录为CSV格式"""
        return self.get_faults(limit=10000)  # 导出最多10000条记录
    
    def export_faults_excel(self) -> bytes:
        """导出所有故障记录为Excel格式"""
        faults = self.get_faults(limit=10000)
        
        # 转换为DataFrame
        data = []
        for fault in faults:
            data.append({
                '厂家': fault.factory_name,
                '设备名称': fault.host_name,
                '故障时间': fault.fault_time.isoformat(),
                '告警名称': fault.alarm_name,
                '告警级别': fault.level or '',
                '设备序列号': fault.device_sn or '',
                '处理结果': fault.resolution or '',
                '恢复时间': fault.recovery_time.isoformat() if fault.recovery_time else '',
                '备注1': fault.note1 or '',
                '备注2': fault.note2 or ''
            })
        
        df = pd.DataFrame(data)
        
        # 使用临时文件方式处理Excel导出
        with tempfile.NamedTemporaryFile(suffix='.xlsx', delete=False) as tmp_file:
            tmp_filename = tmp_file.name
        
        try:
            # 写入Excel文件
            df.to_excel(tmp_filename, sheet_name='故障记录', index=False)
            
            # 读取文件内容
            with open(tmp_filename, 'rb') as f:
                content = f.read()
            
            return content
        finally:
            # 清理临时文件
            try:
                os.unlink(tmp_filename)
            except:
                pass
    
    def import_faults_from_excel(self, file_content: Union[bytes, str], filename: str = "", has_header: bool = True) -> tuple[int, int, list]:
        """从Excel文件导入故障记录
        返回: (成功导入数量, 失败数量, 失败详情列表)
        """
        try:
            print("开始处理Excel文件导入...")
            
            # 如果是字符串，转换为字节
            if isinstance(file_content, str):
                content = file_content.encode('utf-8')
                print("文件内容为字符串，已转换为字节")
            else:
                content = file_content
                print(f"文件内容为字节，大小: {len(content)} 字节")
                
            # 检查是否是CSV文件
            is_csv = filename.lower().endswith('.csv') if filename else False
            
            # 使用临时文件方式处理Excel导入
            with tempfile.NamedTemporaryFile(suffix='.csv' if is_csv else '.xlsx', delete=False) as tmp_file:
                tmp_file.write(content)
                tmp_filename = tmp_file.name
            
            print(f"创建临时文件: {tmp_filename}")
            
            try:
                if is_csv:
                    # 读取CSV文件
                    print("开始读取CSV文件...")
                    # 对于CSV文件，根据has_header参数决定是否将第一行作为标题行
                    if has_header:
                        df = pd.read_csv(tmp_filename)
                    else:
                        df = pd.read_csv(tmp_filename, header=None)
                        # 为没有标题的CSV文件设置默认列名
                        if len(df.columns) > 0:
                            default_columns = ['厂家', '设备名称', '故障时间', '告警名称', '告警级别', '设备序列号', '处理结果', '恢复时间', '备注1', '备注2']
                            # 确保列数不超过默认列数
                            if len(df.columns) <= len(default_columns):
                                df.columns = default_columns[:len(df.columns)]
                else:
                    # 读取Excel文件，显式指定日期列的解析方式
                    print("开始读取Excel文件...")

                    # 读取Excel文件内容到pandas DataFrame
                    # 指定特定列的数据类型为字符串，避免自动推断导致的数据类型问题
                    # 设备序列号、处理结果、备注1、备注2这些列强制转换为字符串类型
                    df = pd.read_excel(
                        tmp_filename, 
                        dtype={
                            '设备序列号': str,      # 设备序列号列转为字符串
                            '处理结果': str,        # 处理结果列转为字符串
                            '备注1': str,          # 备注1列转为字符串
                            '备注2': str           # 备注2列转为字符串
                        }
                    )
                print(f"成功读取文件，数据形状: {df.shape}")
                print(f"列标题: {list(df.columns)}")
                print(f"前5行数据:\n{df.head()}")
            except Exception as e:
                error_msg = f"读取文件失败: {str(e)}"
                print(error_msg)
                import traceback
                traceback.print_exc()
                raise Exception(error_msg)
            finally:
                # 清理临时文件
                try:
                    os.unlink(tmp_filename)
                    print(f"已清理临时文件: {tmp_filename}")
                except Exception as e:
                    print(f"清理临时文件失败: {str(e)}")
            
            # 检查DataFrame是否为空
            if df.empty:
                print("文件为空")
                raise Exception("文件为空，请检查文件内容")
                
            success_count = 0
            error_count = 0
            error_details = []  # 收集错误详情
            
            # 获取数据库连接用于重复检查和数据插入
            conn = get_db_connection()
            cursor = conn.cursor()
            
            # 遍历每一行数据
            for index, row in df.iterrows():
                try:
                    print(f"处理第{index+1}行数据...")
                    
                    # 打印当前行数据用于调试
                    print(f"第{index+1}行原始数据: {dict(row)}")
                    
                    # 处理列标题映射 - 增强映射功能以支持更多列名
                    factory_name = str(row.get('厂家', '')).strip()
                    if factory_name.lower() == 'nan':
                        factory_name = ''
                    
                    host_name = str(row.get('设备名称', '')).strip()
                    if host_name.lower() == 'nan':
                        host_name = ''
                    
                    # 增加对"现象"列的映射支持
                    alarm_name = str(row.get('告警名称', '')).strip()
                    if alarm_name.lower() == 'nan':
                        alarm_name = ''
                    
                    device_sn = str(row.get('设备序列号', '')).strip()
                    if device_sn.lower() == 'nan':
                        device_sn = ''
                    
                    # 增加对"处理"列的映射支持
                    resolution = str(row.get('处理结果', '')).strip()
                    if resolution.lower() == 'nan':
                        resolution = ''
                    
                    # 增加对"处理恢复时间"列的映射支持
                    recovery_time_str = row.get('恢复时间', '') 
                    
                    print(f"第{index+1}行映射后数据 - 厂家: '{factory_name}', 设备名称: '{host_name}', 告警名称: '{alarm_name}'")
                    
                    # 如果必需字段为空，跳过该行
                    if not factory_name.strip() or not host_name.strip() or not alarm_name.strip():
                        error_msg = f"第{index+1}行数据缺少必需字段（厂家: '{factory_name}', 设备名称: '{host_name}', 告警名称: '{alarm_name}'）"
                        print(error_msg)
                        error_details.append({
                            "row": index + 1,
                            "error": "缺少必需字段",
                            "details": f"厂家: '{factory_name}', 设备名称: '{host_name}', 告警名称: '{alarm_name}'"
                        })
                        error_count += 1
                        continue
                    
                    # 处理故障时间 - 添加验证：如果故障时间为空则报错
                    fault_time_str = row.get('故障时间')
                    print(f"第{index+1}行故障时间原始值: {fault_time_str} (类型: {type(fault_time_str)})")
                    
                    # 检查故障时间是否为空，如果为空则直接抛出异常
                    if fault_time_str is None or str(fault_time_str).strip() == '':
                        error_msg = f"第{index+1}行故障时间为空，请填写故障时间"
                        print(error_msg)
                        error_details.append({
                            "row": index + 1,
                            "error": "故障时间为空",
                            "details": error_msg
                        })
                        error_count += 1
                        continue
                    
                    # 处理Excel日期格式（只有在故障时间不为空时才执行）
                    fault_time = None
                    if isinstance(fault_time_str, (int, float)):
                        # 如果是数字，假设它是Excel日期序列号
                        try:
                            # Check if it's a valid number
                            if np.isnan(float(fault_time_str)):
                                raise ValueError("故障时间为NaN")
                            else:
                                days_offset = float(fault_time_str)
                                # Ensure it's a reasonable date (not negative or too large)
                                if days_offset > 0 and days_offset < 100000:  # reasonable range
                                    fault_time = datetime(1899, 12, 30) + timedelta(days=days_offset)
                                    print(f"第{index+1}行故障时间(Excel序列号): {fault_time}")
                                else:
                                    raise ValueError("故障时间超出合理范围")
                        except (ValueError, TypeError) as e:
                            error_msg = f"第{index+1}行故障时间格式错误: {e}"
                            print(error_msg)
                            error_details.append({
                                "row": index + 1,
                                "error": "故障时间格式错误",
                                "details": str(e)
                            })
                            error_count += 1
                            continue
                    else:
                        # 转换为字符串再解析
                        try:
                            parsed_time = pd.to_datetime(str(fault_time_str))
                            print(f"第{index+1}行解析后的时间: {parsed_time} (类型: {type(parsed_time)})")
                            # Convert to datetime if it's a Timestamp
                            if hasattr(parsed_time, 'to_pydatetime') and callable(getattr(parsed_time, 'to_pydatetime', None)):
                                # Safe call to to_pydatetime
                                converted_time = getattr(parsed_time, 'to_pydatetime')()
                                if isinstance(converted_time, datetime):
                                    fault_time = converted_time
                                    print(f"第{index+1}行转换为datetime: {fault_time}")
                                else:
                                    raise ValueError("故障时间转换失败")
                            else:
                                # If it's already a datetime, use it directly
                                if isinstance(parsed_time, datetime):
                                    fault_time = parsed_time
                                    print(f"第{index+1}行已经是datetime类型: {fault_time}")
                                else:
                                    raise ValueError("故障时间类型不匹配")
                        except Exception as e:
                            error_msg = f"第{index+1}行故障时间解析失败: {e}"
                            print(error_msg)
                            error_details.append({
                                "row": index + 1,
                                "error": "故障时间解析失败",
                                "details": str(e)
                            })
                            error_count += 1
                            continue
                    
                    # 确保故障时间已正确设置
                    if fault_time is None:
                        error_msg = f"第{index+1}行故障时间处理失败"
                        print(error_msg)
                        error_details.append({
                            "row": index + 1,
                            "error": "故障时间处理失败",
                            "details": error_msg
                        })
                        error_count += 1
                        continue
                    
                    # 处理恢复时间
                    recovery_time_str = row.get('恢复时间')
                    print(f"第{index+1}行恢复时间原始值: {recovery_time_str} (类型: {type(recovery_time_str)})")
                    recovery_time = None
                    if recovery_time_str is not None and str(recovery_time_str).strip() != '':
                        # 处理Excel日期格式
                        if isinstance(recovery_time_str, (int, float)):
                            # 如果是数字，假设它是Excel日期序列号
                            try:
                                # Check if it's a valid number
                                if not np.isnan(float(recovery_time_str)):
                                    days_offset = float(recovery_time_str)
                                    # Ensure it's a reasonable date (not negative or too large)
                                    if days_offset > 0 and days_offset < 100000:  # reasonable range
                                        recovery_time = datetime(1899, 12, 30) + timedelta(days=days_offset)
                                        print(f"第{index+1}行恢复时间(Excel序列号): {recovery_time}")
                                else:
                                    print(f"第{index+1}行恢复时间超出合理范围，忽略该时间")
                            except (ValueError, TypeError) as e:
                                print(f"第{index+1}行恢复时间格式错误: {e}，忽略该时间")
                                pass
                        else:
                            # 转换为字符串再解析
                            try:
                                parsed_time = pd.to_datetime(str(recovery_time_str))
                                print(f"第{index+1}行解析后的恢复时间: {parsed_time} (类型: {type(parsed_time)})")
                                # Convert to datetime if it's a Timestamp
                                if hasattr(parsed_time, 'to_pydatetime') and callable(getattr(parsed_time, 'to_pydatetime', None)):
                                    # Safe call to to_pydatetime
                                    converted_time = getattr(parsed_time, 'to_pydatetime')()
                                    if isinstance(converted_time, datetime):
                                        recovery_time = converted_time
                                        print(f"第{index+1}行恢复时间转换为datetime: {recovery_time}")
                                else:
                                    # If it's already a datetime, use it directly
                                    if isinstance(parsed_time, datetime):
                                        recovery_time = parsed_time
                                        print(f"第{index+1}行恢复时间已经是datetime类型: {recovery_time}")
                            except Exception as e:
                                print(f"第{index+1}行恢复时间解析失败: {e}，忽略该时间")
                    
                    # 处理可选字段
                    level = None
                    resolution = None
                    note1 = None
                    note2 = None
                    
                    level_val = row.get('告警级别')
                    if level_val is not None and str(level_val).strip() != '' and str(level_val).strip().lower() != 'nan':
                        level = str(level_val)
                    
                    if device_sn is not None and str(device_sn).strip() != '' and str(device_sn).strip().lower() != 'nan':
                        device_sn = str(device_sn)
                    else:
                        device_sn = None
                    
                    resolution_val = row.get('处理结果')
                    if resolution_val is not None and str(resolution_val).strip() != '' and str(resolution_val).strip().lower() != 'nan':
                        resolution = str(resolution_val)
                    
                    note1_val = row.get('备注1')
                    if note1_val is not None and str(note1_val).strip() != '' and str(note1_val).strip().lower() != 'nan':
                        note1 = str(note1_val)
                    
                    note2_val = row.get('备注2')
                    if note2_val is not None and str(note2_val).strip() != '' and str(note2_val).strip().lower() != 'nan':
                        note2 = str(note2_val)
                    
                    print(f"第{index+1}行准备创建故障记录对象...")
                    
                    # 创建故障记录对象
                    fault_data = FaultCreate(
                        factory_name=factory_name,
                        host_name=host_name,
                        fault_time=fault_time,
                        alarm_name=alarm_name,
                        level=level,
                        device_sn=device_sn,
                        resolution=resolution,
                        recovery_time=recovery_time,
                        note1=note1,
                        note2=note2
                    )
                    
                    print(f"第{index+1}行创建故障记录对象成功")
                    
                    # 在保存到数据库之前，检查是否已存在相同的记录（基于厂家名、设备名称、故障时间和告警名称）
                    check_query = '''
                        SELECT COUNT(*) FROM fault_statistics 
                        WHERE factory_name = ? AND host_name = ? AND fault_time = ? AND alarm_name = ?
                    '''
                    cursor.execute(check_query, (factory_name, host_name, fault_time.isoformat(), alarm_name))
                    existing_count = cursor.fetchone()[0]
                    
                    if existing_count > 0:
                        # 如果记录已存在，跳过导入
                        print(f"第{index+1}行数据已存在，跳过导入: {factory_name} - {host_name} - {fault_time} - {alarm_name}")
                        error_details.append({
                            "row": index + 1,
                            "error": "重复记录",
                            "details": f"记录已存在: {factory_name} - {host_name} - {fault_time} - {alarm_name}"
                        })
                        error_count += 1
                        continue
                    
                    # 使用现有连接保存到数据库
                    print(f"第{index+1}行保存到数据库...")
                    self.create_fault_with_connection(conn, fault_data)
                    success_count += 1
                    print(f"第{index+1}行数据导入成功: {factory_name} - {host_name}")
                except Exception as e:
                    print(f"导入第{index+1}行记录时出错: {e}")
                    import traceback
                    traceback.print_exc()
                    error_details.append({
                        "row": index + 1,
                        "error": "未知错误",
                        "details": str(e)
                    })
                    error_count += 1
            
            # 关闭数据库连接
            conn.close()
            
            print(f"导入完成 - 成功: {success_count}, 失败: {error_count}")
            return success_count, error_count, error_details
        except Exception as e:
            print(f"解析文件时出错: {e}")
            import traceback
            traceback.print_exc()
            return 0, 1, [{"row": 0, "error": "文件解析错误", "details": str(e)}]  # 返回失败结果而不是重新抛出异常
    
    def execute_sql(self, sql_query: str) -> dict:
        """执行SQL查询语句"""
        conn = get_db_connection()
        cursor = conn.cursor()
        
        try:
            # 检查是否是TRUNCATE语句并转换为DELETE语句
            stripped_query = sql_query.strip()
            if stripped_query.upper().startswith('TRUNCATE'):
                # 将TRUNCATE TABLE table_name转换为DELETE FROM table_name
                # 使用正则表达式提取表名
                import re
                match = re.match(r'^TRUNCATE\s+TABLE\s+(\w+)', stripped_query, re.IGNORECASE)
                if match:
                    table_name = match.group(1)
                    sql_query = f"DELETE FROM {table_name}"
            
            # 执行SQL查询
            cursor.execute(sql_query)
            
            # 如果是SELECT语句，返回结果
            if sql_query.strip().upper().startswith('SELECT'):
                rows = cursor.fetchall()
                # 将结果转换为字典列表
                columns = [description[0] for description in cursor.description]
                result = [dict(zip(columns, row)) for row in rows]
                return {
                    "type": "select",
                    "data": result,
                    "row_count": len(result)
                }
            else:
                # 对于INSERT/UPDATE/DELETE语句，返回影响的行数
                conn.commit()
                return {
                    "type": "update",
                    "row_count": cursor.rowcount
                }
        except Exception as e:
            conn.rollback()
            raise e
        finally:
            conn.close()